from enum import Enum
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import table,MetaData
from sqlalchemy.sql import select,insert,text,func,join,update,delete,asc,desc
from sqlalchemy.engine import Connection,CursorResult,LegacyRow,Transaction
from eim.result.page_result import PageResult
from eim.result.simple_result import SimpleResult

class MatchLogic(Enum):
    """
    查询条件匹配的逻辑枚举

    Args:
        Enum (_type_): 匹配逻辑
    """
    EQ=0# 等于关系
    NQ=1# 不等关系
    GT=2# 大于关系
    LT=3# 小于关系
    GE=4# 大于等于
    LE=5# 小于等于
    LK=6# like关系
    
    @staticmethod
    def from_Name(name:str):
        try:
            for logic in MatchLogic:
                if(logic.name==str.upper(name)):
                    return logic
            raise Exception("无效的条件判定逻辑名称")
        except Exception as es:
            raise es
    
class PartnerRS(Enum):
    """
    查询条件的同级关系

    Args:
        Enum (_type_): 枚举类
    """
    AND = 0 #与关系
    OR =1 # 或关系
    
    @staticmethod
    def from_name(name:str):
        try:
            for rs in PartnerRS:
                if(rs.name==str(name)):
                    return rs
            raise Exception("条件条件关系名称")
        except Exception as es:
            raise es
    

class SearchCondition:
    """
    查询条件工具类
    """
    def __init__(self,fieldName:str,vallogic:MatchLogic,val:str):
        """
        创建查询条件

        Args:
            fieldName (str): 字段名称
            vallogic (str): 字段匹配逻辑
            val (str): 匹配值
        """
        if(not isinstance(vallogic,MatchLogic)):
            raise Exception("请正确使用字段匹配逻辑")
        self.field = fieldName
        self.logic = vallogic
        self.value = val
    
    # 生成检索条件字符串
    @property
    def genCondition(self):
        """
        生成检索条件字符串
        """
        if(self.logic==MatchLogic.EQ):
            return f"{self.field}='{self.value}'"
        elif(self.logic==MatchLogic.NQ):
            return f"{self.field}<>'{self.value}'"
        elif(self.logic==MatchLogic.GT):
            return f"{self.field}>'{self.value}'"
        elif(self.logic==MatchLogic.LT):
            return f"{self.field}<'{self.value}'"
        elif(self.logic==MatchLogic.GE):
            return f"{self.field}>='{self.value}'"
        elif(self.logic==MatchLogic.LE):
            return f"{self.field}<='{self.value}'"
        elif(self.logic==MatchLogic.LK):
            return f"{self.field} like '%{self.value}%'"
        else:
            return ""


class SearchConditionList:
    """
    查询条件列表类
    """
    def __init__(self):
        """
        构造函数
        """
        self._conditionList=[]
    
    # 添加查询条件
    def appendCondition(self,RS:PartnerRS,CD)->int:
        """
        添加新的查询条件到列表类中

        Args:
            RS (PartnerRS): 默认的同级关系
            CD (SearchCondition或SearchConditionList): 查询条件类或列表类

        Returns:
            int: 返回添加的索引
        """
        try:
            if(not isinstance(RS,PartnerRS)):
                raise Exception("请正确选择检索条件同级关系")
            if(not isinstance(CD,SearchCondition)) and (not isinstance(CD,SearchConditionList)):
                raise Exception("添加的检索条件不是有效的检索条件类")
            newcd = {
                'RS': RS.name,
                'CD': CD.genCondition
            }
            self._conditionList.append(newcd)
            return len(self._conditionList)-1
        except Exception as es:
            raise es
    
    def clearConditions(self):
        self._conditionList.clear()
    
    def getList(self)->list:
        """
        获取条件列表

        Returns:
            list: 条件列表
        """
        return self._conditionList
    
    def getConditionStr(self)->str:
        """
        获取查询条件字符串

        Returns:
            str: 返回查询条件字符串
        """
        try:
            result = None
            for itm  in self._conditionList:
                if result == None:
                    result = itm['CD']
                else:
                    rsname = itm['RS']
                    if rsname=='AND':
                        result=f"({result} and {itm['CD']})"
                    elif(rsname=='OR'):
                        result=f"({result} or {itm['CD']})"
            
            return result
        except Exception as es:
            raise es
    

# 公共查询工具
class GlobalSearch:
    """
    公共查询工具
    """
    def __init__(self,db:SQLAlchemy):
        """
        公共查询工具的构造函数
        Args:
            db (SQLALchemy): 数据库操作类
        """
        try:
            self.db = db
            self.con:Connection=db.engine.connect()
            self.tran:Transaction=None
        except Exception as es:
            raise es
    
    def commit(self):
        """
        事务开启条件下提交事务
        """
        try:
            if self.tran!=None:
                self.tran.commit()
                self.tran=None
        except Exception as es:
            raise es
    
    def rollback(self):
        """
        事务开启状态下的事务回滚方法
        """
        try:
            if self.tran!=None:
                self.tran.rollback()
                self.tran=None
        except Exception as es:
            raise es
    
    def beginTrans(self):
        """
        开启事务
        """
        try:
            self.tran=self.con.begin()
        except Exception as es:
            raise es
    
        
    def __getTableByName(self,tblname:str)->table:
        """
        根据数据表名生成sqlalchemy数据表

        Args:
            tblname (str): 数据表名

        Returns:
            table: sqlalchemy数据表
        """
        try:
            metadata:MetaData=MetaData()
            metadata.reflect(bind=self.db.engine)
            datatable:table = metadata.tables[tblname] 
            return datatable
        except Exception as es:
            raise es
        
    
    def pageTableInfo(self,pn:int,ps:int,dtName:str,fields:list,pmlst:SearchConditionList=None)->dict:
        """
        根据条件分页获取指定数据表中的信息,返回分页结果

        Args:
            pn (int): 分页页码
            ps (int): 分页的页面尺寸
            dtname (str): 指定带查询的数据表名称
            fields (list): 带查询的字段列表
            pmlst (SearchConditionList): 分页查询的条件列表类

        Returns:
            dict: 分页结果字典结构
        """
        try:
            # 获取待查数据表对象
            datatable:table = self.__getTableByName(dtName)
            
            # 执行查询过程            
            pn=1 if pn<=0 else pn
            ps=30 if ps<=0  else ps
            offset =(pn-1)*ps
            cds = None
            
            if(pmlst!=None):
                if(not isinstance(pmlst,SearchConditionList)):
                    raise Exception("请确保检索条件类型错误")
                else:
                    cds=pmlst.getConditionStr()
            else:
                cds = None
            
            if(not isinstance(fields,list)):
                raise Exception("字段列表格式不正确")


            from eim.tools.dbtool import DBTool
            dt = DBTool(self.db)
            if cds!=None:
                qry_fd=select(dt.genDBcolumns(datatable,fields)).where(text(cds))
                qry_cnt=select(func.count(datatable.c.ID)).where(text(cds))
            else:
                qry_fd=select(dt.genDBcolumns(datatable,fields))
                qry_cnt=select(func.count(datatable.c.ID))
            
            qry_fd=qry_fd.limit(ps).offset(offset)
            
            # 获取满足条件的记录总数量
            res:CursorResult = self.con.execute(qry_cnt)
            cnt:int = res.scalar()
            
            # 获取满足条件的分页记录
            recList = []
            res:CursorResult = self.con.execute(qry_fd)
            row:LegacyRow=None
            for row in res.fetchall():
                recList.append(row._asdict())
            
            pr = PageResult(pn,ps,cnt,recList)
            return pr.toDict()
        except Exception as es:
            raise es
        
    def appentData(self,dtname:str,vals:list)->int:
        """
        为指定的数据表添加值

        Args:
            dtname (str): 数据表名称
            val (list): 待添加的值列表

        Returns:
            int: 返回受影响行数
        """
        try:
            datatable:table = self.__getTableByName(dtname)
            qry = insert(datatable).values(vals)
            res:CursorResult = self.con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
        
    def upgradeData(self,dtname:str,vals:dict,pmlst:SearchConditionList=None)->int:
        """
        更新数据表数据

        Args:
            dtname (str): 数据表名称
            vals (dict): 待更新数据
            pmlst (SearchConditionList): 更新条件
        Returns:
            int: 返回受影响行数
        """
        try:
            datable:table = self.__getTableByName(dtname)
            cd=pmlst.getConditionStr()
            qry = update(datable).values(vals)
            qry = qry.where(text(cd))
            res:CursorResult = self.con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
        
    def deleteData(self,dtname:str,pmlst:SearchConditionList=None)->int:
        """
        删除数据表中的数据

        Args:
            dtname (str): 数据表名称
            pmlst (SearchConditionList): 执行条件

        Returns:
            int: 受影响行数
        """
        try:
            if(not isinstance(pmlst,SearchConditionList)):
                raise Exception("删除动作必须包含正确的删除条件")
            datatable:table = self.__getTableByName(dtname)
            qry=delete(datatable)
            qry = qry.where(text(pmlst.getConditionStr()))
            res:CursorResult = self.con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
    
    def getData(self,dtname:str,fields:list=None)->dict:
        """
        获取指定的数据表格全部数据

        Args:
            dtname (str): 数据表格名称
            fields (list, optional): 待查数据的数据列. Defaults to None.
        Returns:
            dict: 返回数据集
        """
        try:
            datatable:table = self.__getTableByName(dtname)
            from eim.tools.dbtool import DBTool
            dt = DBTool(self.db)
            dbcols = dt.genDBcolumns(datatable,fields)
            qry=select(dbcols)
            res:CursorResult = self.con.execute(qry)
            result:list=[]
            for row in res.fetchall():
                result.append(row._asdict())
            return result
        except Exception as es:
            raise es
    
    def getRecordCnt(self,tblname:str,pmlst:SearchConditionList)->int:
        """
        通过特定条件获取数据表的记录数量

        Args:
            tblname (str): 数据表名称 
            pmlst (SearchConditionList): 检索条件

        Returns:
            int: 返回检索数量
        """
        try:
            datatable:table = self.__getTableByName(tblname)
            cd = pmlst.getConditionStr()
            from eim.tools.dbtool import DBTool
            dt = DBTool(self.db)
            col = dt.genDBcolumns(datatable,['ID'])
            qry = select(col)
            qry=qry.where(text(cd))
            res:CursorResult = self.con.execute(qry)
            return res.rowcount
        except Exception as es:
            raise es
    
    def getTableData(self,tblname:str,columns:list=None,pmlst:SearchConditionList=None,groupby:bool=False,orderfds:list=None,odtype:str="asc")->list:
        """
        根据条件及数据列列表获取表格数据

        Args:
            tblname (str): 数据表名称
            columns (list, optional): 数据表列表. Defaults to None.
            pmlst (SearchConditionList, optional): 检索条件. Defaults to None.
            grpby (list): 分组检索的列表.默认为空
            orderfds (list): 用于排序的字段列表
        Returns:
            list: 返回结果集
        """
        try:
            from eim.tools.dbtool import DBTool
            dt = DBTool(self.db)
            datatable:table = self.__getTableByName(tblname)
            cd=None
            if pmlst!=None:
                cd = pmlst.getConditionStr()
            else:
                cd=""
                
            cols=None
            if(columns!=None):
                
                cols=dt.genDBcolumns(datatable,columns)
            else:
                cols = datatable
                
            qry = select(cols)
            qry = qry.where(text(cd))
            if(groupby):
                for item in cols:
                    qry = qry.group_by(item)
            if orderfds!=None:
                for odfd in orderfds:
                    if(odtype=="asc"):
                        qry=qry.order_by(asc(odfd))
                    else:
                        qry=qry.order_by(desc(odfd))
            
            res:CursorResult = self.con.execute(qry)
            result = []
            for row in res.fetchall():
                result.append(row._asdict())
            
            return result
            
        except Exception as es:
            raise es
    
    def getTableScalar(self,tblname:str,columns:list=None,pmlst:SearchConditionList=None):
        """
        根据条件及数据列列表获取结果集的第一行第一列数据

        Args:
            tblname (str): 数据表名称
            columns (list, optional): 数据表列表. Defaults to None.
            pmlst (SearchConditionList, optional): 检索条件. Defaults to None.

        Returns:
            返回结果集
        """
        try:
            datatable:table = self.__getTableByName(tblname)
            cd=None
            if pmlst!=None:
                cd = pmlst.getConditionStr()
            else:
                cd=""
                
            cols=None
            if(columns!=None):
                from eim.tools.dbtool import DBTool
                dt = DBTool(self.db)
                cols=dt.genDBcolumns(datatable,columns)
            else:
                cols = datatable
                
            qry = select(cols)
            qry = qry.where(text(cd))
            res:CursorResult = self.con.execute(qry)
            return res.scalar()   
        except Exception as es:
            raise es
    
    def executeText(self,sqltext:str):
        """
        执行sql语句

        Args:
            sqltext (str): 待执行的sql语句
        """
        try:
            return self.con.execute(text(sqltext))
        except Exception as es:
            raise es
        
    